﻿using System;
using System.Text;
using System.Web.Security;
using System.Security.Cryptography;
using System.Configuration.Provider;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Data;
using System.Configuration;
using System.Web.Configuration;

namespace Pakxel.Framework
{
    public sealed class MSProvider : MembershipProvider
    {
        // System.Web.Security.MembershipProvider properties.
        private int pNewPasswordLength;
        private bool pEnablePasswordReset;
        private bool pEnablePasswordRetrieval;
        private bool pRequiresQuestionAndAnswer;
        private bool pRequiresUniqueEmail;
        private int pPasswordAttemptWindow;
        private int pMinRequiredNonAlphanumericCharacters;
        private int pMinRequiredPasswordLength;
        private string pPasswordStrengthRegularExpression;
        private MembershipPasswordFormat pPasswordFormat;
        private bool pWriteExceptionsToEventLog;
        private int pMaxInvalidPasswordAttempts;

        private string connectionString;

        #region Custom Retrieve Properties
        // A helper function to retrieve config values from the configuration file.
        private string GetConfigValue(string configValue, string defaultValue)
        {
            if (String.IsNullOrEmpty(configValue))
                return defaultValue;

            return configValue;
        }

        /// <summary>Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.</summary>
        /// <param name="password">Cadena a encriptar</param>
        /// <returns>Cadena encriptada.</returns>
        private string EncodePassword(string password)
        {
            string encodedPassword = "";
            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    encodedPassword = password;
                    break;
                case MembershipPasswordFormat.Encrypted:
                    encodedPassword = Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    using (MD5 md5cryp = new MD5CryptoServiceProvider())
                    {
                        byte[] res = md5cryp.ComputeHash(Encoding.UTF8.GetBytes(password));
                        for (int i = 0; i < res.Length; i++)
                            encodedPassword += string.Format("{0:x2}", res[i]);
                    }
                    break;
                default:
                    throw new ProviderException("Formato de password no soportado");
            }

            return encodedPassword;
        }

        /// <summary>Decrypts or leaves the password clear based on the PasswordFormat.</summary>
        /// <param name="encodedPassword">Excrypted password</param>
        /// <param name="Format">Especified Format to decrypt</param>
        /// <returns>Unencrypted string.</returns>
        private string UnEncodePassword(string encodedPassword)
        {
            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    return encodedPassword;
                case MembershipPasswordFormat.Encrypted:
                    return Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(encodedPassword)));
                case MembershipPasswordFormat.Hashed:
                    throw new ProviderException("Cannot unencode a hashed password.");
                default:
                    throw new ProviderException("Unsupported password format.");
            }
        }

        /// <summary>
        /// Compares password values based on the MembershipPasswordFormat.
        /// </summary>
        /// <param name="password">Password to compare</param>
        /// <param name="dbpassword">Password retrieved from database</param>
        /// <returns>check if password is valid compared with database.</returns>
        private bool CheckPassword(string password, string dbpassword)
        {
            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Encrypted:
                    dbpassword = UnEncodePassword(dbpassword); break;
                case MembershipPasswordFormat.Hashed:
                    password = EncodePassword(password); break;
                default:
                    break;
            }

            return (password == dbpassword);
        }

        /// <summary>
        /// A helper function that writes exception detail to the event log. Exceptions
        /// are written to the event log as a security measure to avoid private database
        /// details from being returned to the browser. If a method does not return a status
        /// or boolean indicating the action succeeded or failed, a generic exception is also 
        /// thrown by the caller.
        /// </summary>
        /// <param name="e">Exception</param>
        /// <param name="action">action where the exception occurred.</param>
        private void WriteToEventLog(Exception e, string action)
        {
            using (System.Diagnostics.EventLog log = new System.Diagnostics.EventLog { Source = "MSProvider", Log = "Application" })
            {
                //log.WriteEntry(string.Format("An exception occurred communicating with the data source.\n\n" +
                                             //"Action: {0}\n\n" +
                                             //"Exception: {1}", action, e));
            }
        }

        /// <summary>
        /// Converts a hexadecimal string to a byte array. Used to convert encryption key values from the configuration.
        /// </summary>
        /// <param name="hexString"></param>
        /// <returns></returns>    
        private byte[] HexToByte(string hexString)
        {
            byte[] returnBytes = new byte[hexString.Length / 2];
            for (int i = 0; i < returnBytes.Length; i++)
                returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
            return returnBytes;
        }
        #endregion

        public override void Initialize(string name, NameValueCollection config)
        {
            // Verify that config isn't null
            if (config == null)
                throw new ArgumentNullException("config");

            // Assign the provider a default name if it doesn't have one
            if (String.IsNullOrEmpty(name))
                name = "SmartlogisticsMembershipProvider";

            // Add a default "description" attribute to config if the attribute doesn't exist or is empty
            if (string.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "Custom Smartlogistics SQL Provider");
            }

            // Call the base class's Initialize method
            base.Initialize(name, config);

            pMaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
            pPasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
            pMinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"));
            pMinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
            pPasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""));
            pEnablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
            pEnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
            pRequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
            pRequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));
            pWriteExceptionsToEventLog = Convert.ToBoolean(GetConfigValue(config["writeExceptionsToEventLog"], "true"));
            pNewPasswordLength = Convert.ToInt32(GetConfigValue(config["newPasswordLength"], "8"));

            switch ((string.IsNullOrEmpty(config["passwordFormat"]) ? "Hashed" : config["passwordFormat"]))
            {
                case "Hashed":
                    pPasswordFormat = MembershipPasswordFormat.Hashed; break;
                case "Encrypted":
                    pPasswordFormat = MembershipPasswordFormat.Encrypted; break;
                case "Clear":
                    pPasswordFormat = MembershipPasswordFormat.Clear; break;
                default:
                    throw new ProviderException("Formato de password no soportado.");
            }

            // Initialize Connection.
            ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

            if (ConnectionStringSettings == null || ConnectionStringSettings.ConnectionString.Trim() == "")
                throw new ProviderException("La cadena de conexion no puede estar vacia.");
            
            
            connectionString = ConnectionStringSettings.ConnectionString;
        }

        #region Propiedades
        private string pApplicationName;
        public override string ApplicationName
        {
            get { return pApplicationName; }
            set { pApplicationName = value; }
        }

        public override string PasswordStrengthRegularExpression
        {
            get { return pPasswordStrengthRegularExpression; }
        }

        public bool WriteExceptionsToEventLog
        {
            get { return pWriteExceptionsToEventLog; }
            set { pWriteExceptionsToEventLog = value; }
        }

        public override bool EnablePasswordReset
        {
            get { return pEnablePasswordReset; }
        }

        public override bool EnablePasswordRetrieval
        {
            get { return pEnablePasswordRetrieval; }
        }

        public override int MaxInvalidPasswordAttempts
        {
            get { return pMaxInvalidPasswordAttempts; }
        }

        public override int MinRequiredPasswordLength
        {
            get { return pMinRequiredNonAlphanumericCharacters; }
        }

        public override int PasswordAttemptWindow
        {
            get { return pPasswordAttemptWindow; }
        }

        public override MembershipPasswordFormat PasswordFormat
        {
            get { return pPasswordFormat; }
        }

        public override bool RequiresQuestionAndAnswer
        {
            get { return pRequiresQuestionAndAnswer; }
        }

        public override int MinRequiredNonAlphanumericCharacters
        {
            get { return pMinRequiredNonAlphanumericCharacters; }
        }

        public int newPasswordLength
        {
            get { return pNewPasswordLength; }
        }

        public override bool RequiresUniqueEmail
        {
            get { return pRequiresUniqueEmail; }
        }



        #endregion

        #region Enumeradores

        private enum Failure
        {
            Password,
            PasswordAnswer
        }
        #endregion

        #region Methods


        /// <summary>
        /// A helper method that performs the checks and updates associated with password failure tracking.
        /// </summary>
        /// <param name="username">Username.</param>
        /// <param name="failureType">Type of failure.</param>
        private void UpdateFailureCount(string username, Failure failureType)
        {
            //throw new NotImplementedException("Por diseño no se cuenta con campos para almacenar las fallas de acceso.");
            /*
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("SELECT FailedPasswordAttemptCount, " +
                                                  "FailedPasswordAttemptWindowStart, " +
                                                  "FailedPasswordAnswerAttemptCount, " +
                                                  "FailedPasswordAnswerAttemptWindowStart " +
                                                  "FROM UsersCFD WHERE Username = @Username AND ApplicationName = @ApplicationName", conn);

                cmd.Parameters.Add("@Username", SqlDbType.VarChar, 255).Value = username;
                cmd.Parameters.Add("@ApplicationName", SqlDbType.VarChar, 255).Value = ApplicationName;

                DateTime windowStart = new DateTime();
                int failureCount = 0;

                try
                {
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();

                            switch (failureType)
                            {
                                case Failure.Password:
                                    failureCount = reader.GetInt32(0);
                                    windowStart = reader.GetDateTime(1);
                                    break;
                                case Failure.PasswordAnswer:
                                    failureCount = reader.GetInt32(2);
                                    windowStart = reader.GetDateTime(3);
                                    break;
                            }
                        }
                        reader.Close();
                    }

                    if (failureCount == 0 || DateTime.Now > windowStart.AddMinutes(PasswordAttemptWindow))
                    {
                        // First password failure or outside of PasswordAttemptWindow. 
                        // Start a new password failure count from 1 and a new window starting now.
                        switch (failureType)
                        {
                            case Failure.Password:
                                cmd.CommandText = "UPDATE UsersCFD " +
                                                  "  SET FailedPasswordAttemptCount = @Count, " +
                                                  "      FailedPasswordAttemptWindowStart = @WindowStart " +
                                                  "  WHERE Username = @Username AND ApplicationName = @ApplicationName";
                                break;
                            case Failure.PasswordAnswer:
                                cmd.CommandText = "UPDATE UsersCFD " +
                                                  "  SET FailedPasswordAnswerAttemptCount = @Count, " +
                                                  "      FailedPasswordAnswerAttemptWindowStart = @WindowStart " +
                                                  "  WHERE Username = @Username AND ApplicationName = @ApplicationName";
                                break;
                        }
                        cmd.Parameters.Clear();

                        cmd.Parameters.Add("@Count", SqlDbType.Int).Value = 1;
                        cmd.Parameters.Add("@WindowStart", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@Username", SqlDbType.VarChar, 255).Value = username;
                        cmd.Parameters.Add("@ApplicationName", SqlDbType.VarChar, 255).Value = ApplicationName;

                        if (cmd.ExecuteNonQuery() < 0)
                            throw new ProviderException("Unable to update failure count and window start.");
                    }
                    else
                    {
                        if (failureCount++ >= MaxInvalidPasswordAttempts)
                        {
                            // Password attempts have exceeded the failure threshold. Lock out the user.
                            cmd.CommandText = "UPDATE UsersCFD " +
                                              "  SET IsLockedOut = @IsLockedOut, LastLockedOutDate = @LastLockedOutDate " +
                                              "  WHERE Username = @Username AND ApplicationName = @ApplicationName";

                            cmd.Parameters.Clear();

                            cmd.Parameters.Add("@IsLockedOut", SqlDbType.Bit).Value = true;
                            cmd.Parameters.Add("@LastLockedOutDate", SqlDbType.DateTime).Value = DateTime.Now;
                            cmd.Parameters.Add("@Username", SqlDbType.VarChar, 255).Value = username;
                            cmd.Parameters.Add("@ApplicationName", SqlDbType.VarChar, 255).Value = ApplicationName;

                            if (cmd.ExecuteNonQuery() < 0)
                                throw new ProviderException("Unable to lock out user.");
                        }
                        else
                        {
                            // Password attempts have not exceeded the failure threshold. Update
                            // the failure counts. Leave the window the same.
                            switch (failureType)
                            {
                                case Failure.Password:
                                    cmd.CommandText = "UPDATE UsersCFD " +
                                                  "  SET FailedPasswordAttemptCount = @Count" +
                                                  "  WHERE Username = @Username AND ApplicationName = @ApplicationName";
                                    break;
                                case Failure.PasswordAnswer:
                                    cmd.CommandText = "UPDATE UsersCFD " +
                                          "  SET FailedPasswordAnswerAttemptCount = @Count" +
                                          "  WHERE Username = @Username AND ApplicationName = @ApplicationName";
                                    break;
                            };
                            cmd.Parameters.Clear();

                            cmd.Parameters.Add("@Count", SqlDbType.Int).Value = failureCount;
                            cmd.Parameters.Add("@Username", SqlDbType.VarChar, 255).Value = username;
                            cmd.Parameters.Add("@ApplicationName", SqlDbType.VarChar, 255).Value = ApplicationName;

                            if (cmd.ExecuteNonQuery() < 0)
                                throw new ProviderException("Unable to update failure count.");
                        }
                    }
                }
                catch (SqlException e)
                {
                    if (pWriteExceptionsToEventLog)
                    {
                        WriteToEventLog(e, "UpdateFailureCount");

                        throw new ProviderException("An exception occurred. Please check the Event Log.");
                    }
                    else
                        throw e;
                }
                finally { conn.Close(); }
            }*/
        }

        private MembershipUser GetUserFromReader(SqlDataReader reader)
        {
            DateTime lastLoginDate = new DateTime();
            if (reader.GetValue(3) != DBNull.Value)
                lastLoginDate = reader.GetDateTime(3);

            return new MembershipUser(this.Name,
                                        reader.GetString(0),
                                        null,
                                        string.Empty,
                                        string.Empty,
                                        reader.GetString(1),
                                        true,
                                        (reader.GetString(2) == "t"),
                                        new DateTime(),
                                        lastLoginDate,
                                        new DateTime(),
                                        new DateTime(),
                                        new DateTime());
        }

        private int GetAffectedRows(SqlCommand Cmd, string Action)
        {
            int rowsAffected = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                Cmd.Connection = conn;
                try
                {
                    conn.Open();

                    rowsAffected = Cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(ex, Action);

                        throw new ProviderException("An exception occurred. Please check the Event Log.");
                    }
                    else
                        throw ex;
                }
                finally { conn.Close(); }
            }
            return rowsAffected;
        }

        private MembershipUserCollection FindUsers(SqlCommand Cmd, string WhereUser, string Action, int pageIdx, int pageSze, out int totalRecs)
        {
            MembershipUserCollection users = new MembershipUserCollection();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlDataReader reader = null;
                Cmd.Connection = conn;
                totalRecs = 0;
                WhereUser = (string.IsNullOrEmpty(WhereUser) ? "" : " WHERE " + WhereUser);

                try
                {
                    Cmd.CommandText = String.Format("SELECT T002UserName, T002Nombre, T002Activo, T002UltimoAcceso FROM T002 {0} ORDER BY T002UserName Asc", WhereUser);

                    conn.Open();

                    reader = Cmd.ExecuteReader();

                    if (!reader.HasRows) return users;
                    
                    int counter = 0;
                    int startIndex = pageSze * pageIdx;
                    int endIndex = startIndex + pageSze - 1;

                    while (reader.Read())
                    {
                        if (counter >= startIndex) { 
                            users.Add(GetUserFromReader(reader));
                            totalRecs++;
                        }

                        if (counter >= endIndex) 
                            Cmd.Cancel();

                        counter++;
                    }
                }
                catch (SqlException ex)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(ex, Action);

                        throw new ProviderException("An exception occurred. Please check the Event Log.");
                    }
                    else
                        throw ex;
                }
                finally
                {
                    if (reader != null) { reader.Close(); }

                    conn.Close();
                }
            }

            return users;
        }

        private MembershipUser FindUser(SqlCommand Cmd, bool IsActivo, string Action)
        {
            MembershipUser u = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlDataReader reader = null;
                Cmd.Connection = conn;
                try
                {
                    conn.Open();

                    reader = Cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        reader.Read();
                        u = GetUserFromReader(reader);

                        if (IsActivo)
                        {
                            SqlCommand updateCmd = new SqlCommand("UPDATE T002 SET T002UltimoAcceso = @Lastaccess " +
                                Cmd.CommandText.Substring(Cmd.CommandText.IndexOf("WHERE")), conn);

                            updateCmd.Parameters.Add("@Lastaccess", SqlDbType.DateTime).Value = DateTime.Now;
                            foreach (SqlParameter Parm in Cmd.Parameters)
                                updateCmd.Parameters.Add(Parm.ParameterName, Parm.SqlDbType, Parm.Size).Value = Parm.Value;

                            updateCmd.ExecuteNonQuery();
                        }
                    }

                }
                catch (SqlException ex)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(ex, Action);

                        throw new ProviderException("An exception occurred. Please check the Event Log.");
                    }
                    else
                        throw ex;
                }
                finally
                {
                    if (reader != null) { reader.Close(); }

                    conn.Close();
                }
            }
            return u;
        }

        #endregion

        //Pendiente corregir consultas y logica
        /// <summary>Devuelve la contraseña del usuario especificado.</summary>
        /// <param name="username">Nombre del usuario</param>
        /// <returns>Contraseña.</returns>
        public string GetPassword(string username)
        {
            if (!EnablePasswordRetrieval)
                throw new ProviderException("Recuperacion de contraseñas no habilitada.");

            if (PasswordFormat == MembershipPasswordFormat.Hashed)
                throw new ProviderException("Las contraseñas encriptadas por Hash no se pueden recuperar.");

            string password = "";

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("SELECT T002Password, T002Activo FROM T002 WHERE T002UserName = @Username", conn);
                cmd.Parameters.Add("@Username", SqlDbType.VarChar, 255).Value = username;

                try
                {
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();

                            if (reader.GetString(1) == "S")
                                throw new MembershipPasswordException("El usuario esta bloqueado.");

                            password = reader.GetString(0);
                        }
                        else
                            throw new MembershipPasswordException("No se encontro al usuario especificado.");
                    }
                }
                catch (SqlException e)
                {
                    if (pWriteExceptionsToEventLog)
                    {
                        WriteToEventLog(e, "GetPassword");

                        throw new ProviderException("An exception occurred. Please check the Event Log.");
                    }
                    else
                        throw e;
                }
                finally { conn.Close(); }
            }

            if (PasswordFormat == MembershipPasswordFormat.Encrypted)
                password = UnEncodePassword(password);

            return password;
        }

        public override string GetPassword(string username, string answer)
        {
            throw new NotImplementedException("No se puede obtener una contraseña incluyendo respuesta.");
        }

        public override string GetUserNameByEmail(string email)
        {
            throw new NotImplementedException("Los usuarios no cuentan con correo electronico por diseño.");
        }

        public string ResetPassword(string username)
        {
            if (!EnablePasswordReset)
                throw new NotSupportedException("Password reset is not enabled.");

            //No se requiere pregunta para el password
            /*if (answer == null && RequiresQuestionAndAnswer) {
                UpdateFailureCount(username, Failure.PasswordAnswer);
                throw new ProviderException("Password answer required for password reset.");
            }*/

            string newPassword = Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters);

            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
            OnValidatingPassword(args);

            if (args.Cancel)
                if (args.FailureInformation != null)
                    throw args.FailureInformation;
                else
                    throw new MembershipPasswordException("Cambio de contraseña cancelado debido a una falla en la validacion de la contraseña.");

            int rowsAffected = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                //SqlCommand cmd = new SqlCommand("SELECT PasswordAnswer, IsLockedOut FROM UsersCFD WHERE Username = @Username", conn);
                //cmd.Parameters.Add("@Username", SqlDbType.VarChar, 255).Value = username;
                //string passwordAnswer = "";
                //SqlDataReader reader = null;

                try
                {

                    SqlCommand updateCmd = new SqlCommand("UPDATE T002 SET T002Password = @Password WHERE T002UserName = @Username AND T002Activo = @activo", conn);

                    updateCmd.Parameters.Add("@Password", SqlDbType.VarChar, 12).Value = EncodePassword(newPassword);
                    updateCmd.Parameters.Add("@Username", SqlDbType.VarChar, 15).Value = username;
                    updateCmd.Parameters.Add("@Activo", SqlDbType.Char, 1).Value = 'S';

                    conn.Open();

                    /*reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
                    if (reader.HasRows) {
                        reader.Read();
                        if (reader.GetBoolean(1))
                            throw new MembershipPasswordException("The supplied user is locked out.");
                        passwordAnswer = reader.GetString(0);
                    } else
                        throw new MembershipPasswordException("The supplied user name is not found.");
                    if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer)) {
                        UpdateFailureCount(username, Failure.PasswordAnswer);
                        throw new MembershipPasswordException("Incorrect password answer.");
                    }*/

                    rowsAffected = updateCmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(ex, "ResetPassword");

                        throw new ProviderException("An exception occurred. Please check the Event Log.");
                    }
                    else
                        throw ex;
                }
                finally
                {
                    //if (reader != null) { reader.Close(); }
                    conn.Close();
                }
            }

            if (rowsAffected > 0)
                return newPassword;
            else
                throw new MembershipPasswordException("Usuario no encontrado, esta bloqueado o no se pudo resetear la contraseña.");
        }

        public override string ResetPassword(string username, string answer)
        {
            throw new NotImplementedException("El reseteo de ocntraseñas no esta implementado incluyendo respuesta por diseño.");
        }

        public override bool ChangePassword(string username, string oldPassword, string newPassword)
        {
            if (!ValidateUser(username, oldPassword))
                return false;

            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
            OnValidatingPassword(args);

            if (args.Cancel)
                if (args.FailureInformation != null)
                    throw args.FailureInformation;
                else
                    throw new MembershipPasswordException("Cambio de contraseña cancelada debido a una falla en la validacion de la nueva contraseña.");

            using (SqlCommand cmd = new SqlCommand("UPDATE T002 SET T002Password = @Password WHERE T002UserName = @Username"))
            {
                cmd.Parameters.Add("@Password", SqlDbType.VarChar, 12).Value = EncodePassword(newPassword);
                cmd.Parameters.Add("@Username", SqlDbType.VarChar, 15).Value = username;

                return (GetAffectedRows(cmd, "ChangePassword") > 0);
            }
        }

        public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
        {
            throw new NotImplementedException("No se admite el cambio de Preguntas por diseño.");
        }

        public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
        {
            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);
            OnValidatingPassword(args);

            if (args.Cancel) 
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }

            if (GetUser(username, false) == null)
            {
                DateTime createDate = DateTime.Now;

                using (SqlCommand cmd = new SqlCommand("INSERT INTO T002 (T002UserName, T002Password, T002Nombre, T002UltimoAcceso, T002Activo)" +
                          " VALUES (@Username, @Password, @Nombre, @UltimoAcceso, @Activo)"))
                {
                    cmd.Parameters.Add("@Username", SqlDbType.VarChar, 15).Value = username;
                    cmd.Parameters.Add("@Password", SqlDbType.VarChar, 12).Value = EncodePassword(password);
                    cmd.Parameters.Add("@Nombre", SqlDbType.VarChar, 80).Value = username;
                    cmd.Parameters.Add("@UltimoAcceso", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@Activo", SqlDbType.Char, 1).Value = (isApproved ? 'S' : 'N');

                    try
                    {
                        status = (GetAffectedRows(cmd, "CreateUser") > 0 ? MembershipCreateStatus.Success : MembershipCreateStatus.UserRejected);

                        return GetUser(username, false);
                    }
                    catch { status = MembershipCreateStatus.ProviderError; }
                }
            }
            else
                status = MembershipCreateStatus.DuplicateUserName;

            return null;
        }

        public override bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            using (SqlCommand cmd = new SqlCommand("DELETE FROM T002 WHERE T002UserName = @Username"))
            {
                cmd.Parameters.Add("@Username", SqlDbType.VarChar, 15).Value = username;

                return (GetAffectedRows(cmd, "DeleteUser") > 0);
            }
        }

        /// <summary>(No Implementada) Devuelve el usuario en base a su correo electronico.</summary>
        /// <param name="emailToMatch">correo electronico</param>
        /// <param name="pageIndex">Indice de pagina</param>
        /// <param name="pageSize">Tamaño de pagina</param>
        /// <param name="totalRecords">Registros devueltos</param>
        /// <returns>Membresias de los usuarios que cumplen la condicion</returns>
        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            throw new NotImplementedException("Esta caracteristica no esta implementada por diseño.");
        }

        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            using (SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM T002"))
            {
                cmd.Parameters.Add("@UsernameSearch", SqlDbType.VarChar, 15).Value = usernameToMatch;

                return FindUsers(cmd, "T002UserName LIKE @UsernameSearch", "FindUsersByName", pageIndex, pageSize, out totalRecords);
            }
        }

        /// <summary>Devuelve la cantidad de usuarios Activos.</summary>
        /// <returns>Numero de usuarios.</returns>
        public override int GetNumberOfUsersOnline()
        {
            TimeSpan onlineSpan = new TimeSpan(0, Membership.UserIsOnlineTimeWindow, 0);
            DateTime compareTime = DateTime.Now.Subtract(onlineSpan);

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM T002 WHERE T002UltimoAcceso > @CompareDate", conn);
                cmd.Parameters.Add("@CompareDate", SqlDbType.DateTime).Value = compareTime;

                try
                {
                    conn.Open();

                    return (int)cmd.ExecuteScalar();
                }
                catch (SqlException ex)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(ex, "GetNumberOfUsersOnline");

                        throw new ProviderException("An exception occurred. Please check the Event Log.");
                    }
                    else
                        throw ex;
                }
                finally { conn.Close(); }
            }
        }

        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            using (SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM T002"))
            {
                return FindUsers(cmd, "", "GetAllUsers", pageIndex, pageSize, out totalRecords);
            }
        }

        /// <summary>(No implementada) Devuelve la membresia de un usuario por medio de su unica llave.</summary>
        /// <param name="providerUserKey">Llave unica</param>
        /// <param name="userIsOnline">Indica si esta o no activo.</param>
        /// <returns>>Membresia de usuario.</returns>
        public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
        {
            throw new NotImplementedException("Esta opcion no esta permitida por diseño.");
        }

        /// <summary>Devuleve la membresia de un usuario, especificando si está o no activo.</summary>
        /// <param name="username">usuario</param>
        /// <param name="userIsOnline">Indica si esta o no activo.</param>
        /// <returns>Membresia de usuario.</returns>
        public override MembershipUser GetUser(string username, bool userIsOnline)
        {
            using (SqlCommand cmd = new SqlCommand("SELECT T002UserName, T002Nombre, T002Activo, T002UltimoAcceso FROM T002 WHERE T002Username = @Username"))
            {
                cmd.Parameters.Add("@Username", SqlDbType.VarChar, 15).Value = username;

                return FindUser(cmd, userIsOnline, "GetUser(string, Boolean)");
            }
        }

        public override void UpdateUser(MembershipUser user)
        {
            using (SqlCommand cmd = new SqlCommand("UPDATE T002 SET T002Nombre = @nombre, T002Activo = @active WHERE T002UserName = @Username"))
            {
                cmd.Parameters.Add("@nombre", SqlDbType.VarChar, 80).Value = user.Comment;
                cmd.Parameters.Add("@active", SqlDbType.Char).Value = (user.IsLockedOut ? 'S' : 'N');
                cmd.Parameters.Add("@Username", SqlDbType.VarChar, 15).Value = user.UserName;

                GetAffectedRows(cmd, "UpdateUser");
            }
        }

        public override bool ValidateUser(string username, string password)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("SELECT T002Password, T002Activo FROM T002 WHERE T002UserName = @Username", conn);
                cmd.Parameters.Add("@Username", SqlDbType.VarChar, 8).Value = username;

                SqlDataReader reader = null;
                bool isApproved = false;
                string pwd = "";

                try
                {
                    conn.Open();

                    reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                    if (reader.HasRows)
                    {
                        reader.Read();
                        pwd = reader.GetString(0);
                        isApproved = (reader.GetString(1) == "S");
                    }
                    else
                        return false;

                    reader.Close();

                    //Actualizando la sesion
                    if (isApproved)
                    {
                        if (CheckPassword(password, pwd))
                            using (SqlCommand updateCmd = new SqlCommand("UPDATE T002 SET T002UltimoAcceso = @LastLoginDate WHERE T002UserName = @Username", conn))
                            {
                                updateCmd.Parameters.Add("@LastLoginDate", SqlDbType.DateTime).Value = DateTime.Now;
                                updateCmd.Parameters.Add("@Username", SqlDbType.VarChar, 15).Value = username;

                                updateCmd.ExecuteNonQuery();

                                return true;
                            }
                        else {
                            conn.Close();

                            UpdateFailureCount(username, Failure.Password);
                        }
                    }
                }
                catch (SqlException ex)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(ex, "ValidateUser");

                        throw new ProviderException("An exception occurred. Please check the Event Log.");
                    }
                    else
                        throw ex;
                }
                finally
                {
                    if (reader != null) { reader.Close(); }
                    conn.Close();
                }

            }

            return false;
        }

        public override bool UnlockUser(string userName)
        {
            using (SqlCommand cmd = new SqlCommand("UPDATE T002 SET T002Activo = @activo, WHERE T002UserName = @Username"))
            {
                cmd.Parameters.Add("@Username", SqlDbType.VarChar, 15).Value = userName;
                cmd.Parameters.Add("@activo", SqlDbType.Char, 1).Value = 'S';

                return (GetAffectedRows(cmd, "UnlockUser") > 0);
            }
        }
    }
}
